<?php

function insertInfoSql($date, $status){
    return sprintf("replace into homegarden_oa.order_info(
              `id`,
              `time`,
              `pay_time`,
              `site_id`,
              `site_code`,
              `user_id`,
              `user_name`,
              `user_phone`,
              `delivery_type`,
              `delivery_date`,
              `delivery_address`
            ) SELECT 
            order_from.orderId id,
            order_from.transactionTime `time`,
            order_from.modificationTime pay_time,
            vegetable_lng_lat.id site_id,
            left(vegetable_lng_lat.entityaddressId,6) site_code,
            order_address.user_id user_id,
            order_address.relation_name user_name,
            order_address.relation_phone user_phone,
            order_address.address_type delivery_type,
            '%s' delivery_date,
            order_address.address_des delivery_address 
        FROM 
            homegarden.order_from 
        INNER JOIN homegarden.order_address ON order_address.id = order_from.orderId 
        LEFT JOIN homegarden.vegetable_lng_lat ON vegetable_lng_lat.id = order_address.eid AND abs(vegetable_lng_lat.address_Type - 1) = order_address.address_type 
        WHERE 
            order_from.distributionTime LIKE CONCAT('%s', '%%') 
        AND order_from.`status` = '%d';",$date,$date,$status);
}

function insertBillSql($date, $status){
    return sprintf("replace INTO homegarden_oa.order_bill (
            `order_id`,
            `pay`,
            `active`,
            `coupon`,
            `integral`,
            `balance`,
            `pay_type`,
            `paid`
        ) SELECT
            orderId,
            productPrice,
            activePrice,
            couponMoney,
            vegetableMoney,
            balanceMoney,
            payType,
            relityPrice
        FROM
            homegarden.order_from
        WHERE
            order_from.distributionTime LIKE CONCAT('%s', '%%')
        AND order_from.`status` = '%d';",$date,$date,$status);
}

function insertDetailSql($date, $status){
    return sprintf("replace into homegarden_oa.order_detail(
              `order_id`,
              `time`,
              `pay_time`,
              `delivery_date`,
              `product_id`,
              `product_code`,
              `number`
            ) SELECT 
            order_from.orderId order_id,
            order_from.transactionTime `time`,
            order_from.modificationTime pay_time,
            '%s' delivery_date,
            order_detail.pid product_id,
            order_detail.productId product_code,
            order_detail.productAmout number 
        FROM 
            homegarden.order_from 
        INNER JOIN homegarden.order_detail ON order_detail.orderId = order_from.orderId 
        WHERE 
            order_from.distributionTime LIKE CONCAT('%s', '%%') 
        AND order_from.`status` = '%d';",$date,$date,$status);
}

function insertSiteProductSql($date){
    return sprintf("INSERT INTO homegarden_oa.order_site_product (
                `site_id`,
                `site_code`,
                `worker_x`,
                `worker_y`,
                `worker_z`,
                `product_id`,
                `product_code`,
                `number`,
                `delivery_date`
            ) SELECT
                site.id,
                site.code,
                MAX(CASE task.job WHEN 'x' THEN task.worker_id END) worker_x,
                MAX(CASE task.job WHEN 'y' THEN task.worker_id END) worker_y,
                MAX(CASE task.job WHEN 'z' THEN task.worker_id END) worker_z,
                order_detail.product_id,
                order_detail.product_code,
                SUM(distinct order_detail.number) `number`,
                order_detail.delivery_date
            FROM
                homegarden_oa.order_detail
            INNER JOIN homegarden_oa.order_info ON order_info.id = order_detail.order_id
            LEFT JOIN homegarden_cms.site ON site.id = order_info.site_id
            LEFT JOIN homegarden_oa.task on task.task_id = site.`code`
            where order_detail.delivery_date = '%s' and task.job in ('x','y','z') and task.additional = 0 
            GROUP BY order_detail.order_id;",$date);
}

function insertProductSql($date){
    return sprintf("INSERT INTO homegarden_oa.order_product (
                `date`,
                `product_id`,
                `product_code`,
                `require_package`,
                `require_weight`,
                `require_number`
            ) SELECT
                order_detail.delivery_date date,
                order_detail.product_id,
                order_detail.product_code,
                SUM(order_detail.number),
                FORMAT(
                    (
                        sum(order_detail.number) * product.real_weight
                    ) / 500,
                    2
                ),
                sum(order_detail.number) * product.number
            FROM
                homegarden_oa.order_detail
            LEFT JOIN homegarden_cms.product ON product.id = order_detail.product_id
            WHERE
                delivery_date = '%s'
            GROUP BY
                product_id;",$date);
}

function insertPurchaseRequireSql($date){
    return sprintf("INSERT INTO homegarden_oa.purchase_require (
                `date`,
                `category_id`,
                `number`
            ) SELECT
                product.delivery_date,
                category_class3,
                sum(count) count
            FROM
                (
                    SELECT
                        order_detail.delivery_date,
                        product.category_class3,
                        product.`name`,
                        sum(order_detail.number) * product.number count
                    FROM
                        homegarden_oa.order_detail
                    INNER JOIN homegarden_cms.product ON product.id = order_detail.product_id
                    WHERE
                        order_detail.delivery_date = '%s'
                    GROUP BY
                        product.id
                ) product
            GROUP BY
                category_class3;",$date);
}

function insertPurchaseSql($date){
    return sprintf("INSERT INTO homegarden_oa.purchase (
                `date`,
                `category_id`,
                `type`,
                `require_number`,
                `require_weight`
            ) SELECT 
                purchase_require.date,
                purchase_require.category_id,
                stock.type,
                SUM(purchase_require.number),
                FORMAT(SUM(purchase_require.number * stock.weight * 1.25) / 500, 2)
            FROM
                homegarden_oa.purchase_require
            INNER JOIN homegarden_oa.stock ON stock.category_id = purchase_require.category_id
            WHERE 
                purchase_require.date = '%s' 
            GROUP BY 
                purchase_require.category_id;",$date);
}

function updateWorkerWSql($date){
    return sprintf("UPDATE homegarden_oa.purchase 
            INNER JOIN (
                SELECT 
                    task_id,
                    worker_id
                FROM 
                    homegarden_oa.task 
                WHERE 
                    job = 'w' and additional = '1' 
            ) task ON task.task_id = purchase.category_id 
            SET worker_w = task.worker_id 
            WHERE 
                purchase.date = '%s'",$date);
}

function updateWorkerXSql($date){
    return sprintf("UPDATE homegarden_oa.order_info 
            INNER JOIN (
                SELECT 
                    task_id,
                    worker_id
                FROM 
                    homegarden_oa.task 
                WHERE 
                    job = 'x' 
            ) task ON task.task_id = order_info.site_code 
            SET worker_x = task.worker_id 
            WHERE 
                order_info.delivery_date = '%s'",$date);
}

function updateWorkerYSql($date){
    return sprintf("UPDATE homegarden_oa.order_info 
            INNER JOIN ( 
                SELECT 
                    task_id,
                    worker_id
                FROM 
                    homegarden_oa.task 
                WHERE 
                    job = 'y' 
            ) task ON task.task_id = order_info.site_code 
            SET worker_y = task.worker_id 
            WHERE 
                order_info.delivery_date = '%s'",$date);
}

function updateWorkerZSql($date){
    return sprintf("UPDATE homegarden_oa.order_info
            INNER JOIN ( 
                SELECT 
                    task_id,
                    worker_id,
                    additional 
                FROM 
                    homegarden_oa.task 
                WHERE 
                    job = 'z' 
            ) task ON task.task_id = order_info.site_code and abs(task.additional - 1) = order_info.delivery_type 
            SET worker_z = task.worker_id 
            WHERE 
                order_info.delivery_date = '%s'",$date);
}

function replaceProductByIds($ids){
    return sprintf("REPLACE INTO homegarden.product (
            id,
            product_id,
            product_name,
            product_price,
            product_mainPrice,
            product_standard,
            product_briefDescription,
            product_description,
            product_stock,
            product_number,
            product_unit,
            product_class1,
            product_class2,
            product_class3,
            product_headImage,
            product_bannerImage,
            product_detailImage,
            theme_coverImage,
            theme_bottomImage,
            real_weight,
            activeId,
            issale,
            address,
            cycle,
            reposition,
            is_coupon,
            is_integral
        ) SELECT
            id,
            `code`,
            `name`,
            price,
            main_price,
            standard,
            brief_description,
            description,
            stock,
            number,
            unit,
            category_class1,
            category_class2,
            category_class3,
            thumb_image,
            head_image,
            detail_image,
            corner_image,
            bottom_image,
            real_weight,
            activity_id,
            is_used,
            address,
            cycle,
            reposition,
            is_coupon,
            is_integral
        FROM
            homegarden_cms.product
        LEFT JOIN homegarden_cms.product_stock ON product_stock.product_code = product.`code`
        WHERE
            product.id IN (%s)", implode(',', $ids));
}

function getPresentID($val)
{
    return 'P' . $val;
}


